package com.hgl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.hgl.dao.HglDAO;
import com.hgl.entity.Hgl;
import com.hgl.utils.DBHelper;
import com.hgl.vo.HglVO;

public class DAOiml implements HglDAO {

	@Override
	public int add(Hgl mb) {
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		String sql = "insert into hgl(hgl_name,hgl_grade,hgl_tel,hgl_birth) values(?,?,?,?)";
		PreparedStatement ps = null;
		int result = -1;
		try {

			ps = conn.prepareStatement(sql);
			ps.setString(1, mb.getHglName());
			ps.setInt(2, mb.getHglGrade());
			ps.setString(3, mb.getHglTel());
			ps.setObject(4, mb.getHglBirth());
			result = ps.executeUpdate();
			System.out.println(result);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.close(conn, ps);
		}
		return result;
	}

	@Override
	public int updateById(Hgl mb) {
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		String sql = "UPDATE hgl SET hgl_name =? ,hgl_grade =? ,hgl_tel=?,hgl_birth=? where hgl_id =?";
		PreparedStatement ps = null;
		int result = -1;
		try {
			ps = conn.prepareStatement(sql);

			ps.setString(1, mb.getHglName());
			ps.setInt(2, mb.getHglGrade());
			ps.setString(3, mb.getHglTel());
			ps.setObject(4, mb.getHglBirth());
			ps.setInt(5, mb.getHglId());

			result = ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.close(null, ps);
		}
		return result;
	}

	@Override
	public int deleteById(int id) {
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		String sql = "delete from hgl where hgl_id=?";

		PreparedStatement ps = null;
		int result = -1;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return result;

	}

	@Override
	public List<Hgl> selectById(int id) {
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		String sql = "select *from hgl where hgl_id=" + id;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Hgl> list = new ArrayList<Hgl>();

		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			Hgl mb = new Hgl();
			while (rs.next()) {
				mb.setHglId(rs.getInt("hgl_id"));
				mb.setHglName(rs.getString("hgl_name"));
				mb.setHglGrade(rs.getInt("hgl_grade"));
				mb.setHglTel(rs.getString("hgl_tel"));
				mb.setHglBirth(rs.getDate("hgl_birth"));

				list.add(mb);

			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public List<Hgl> selectALL() {
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		String sql = "select* from hgl";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Hgl> list = new ArrayList<Hgl>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {

				Hgl mb = new Hgl();
				mb.setHglId(rs.getInt("hgl_id"));
				mb.setHglName(rs.getString("hgl_name"));
				mb.setHglGrade(rs.getInt("hgl_grade"));
				mb.setHglTel(rs.getString("hgl_tel"));
				mb.setHglBirth(rs.getDate("hgl_birth"));

				list.add(mb);

			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;

	}

	@Override
	public int countALL() {
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		String sql = "select count(*) from hgl";
		PreparedStatement ps = null;
		ResultSet rs = null;
		int result = -1;

		try {

			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			db.close(conn, ps);
		}


		return result;
	}

	@Override
	public Hgl selectByid(int id) {
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		String sql = "select *from hgl where hgl_id=?";
		Hgl mb = new Hgl();
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				mb.setHglId(rs.getInt("hgl_id"));
				mb.setHglName(rs.getString("hgl_name"));
				mb.setHglGrade(rs.getInt("hgl_grade"));
				mb.setHglTel(rs.getString("hgl_tel"));
				mb.setHglBirth(rs.getDate("hgl_birth"));
				return mb;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;

	}

	@Override
	public List<Hgl> selectBylen(Integer len) {
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		String sql = "select * from hgl limit ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Hgl> list = new ArrayList<Hgl>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				Hgl mb = new Hgl();
				mb.setHglId(rs.getInt("hgl_id"));
				mb.setHglName(rs.getString("hgl_name"));
				mb.setHglGrade(rs.getInt("hgl_grade"));
				mb.setHglTel(rs.getString("hgl_tel"));
				mb.setHglBirth(rs.getDate("hgl_birth"));
				list.add(mb);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public List<Hgl> paging(int current, int rowMax) {
		List<Hgl> list = new ArrayList<Hgl>();
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		String sql = "select * from hgl limit ?,?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, current);
			ps.setObject(2, rowMax);
			rs = ps.executeQuery();
			while (rs.next()) {
				Hgl h = new Hgl();
				h.setHglId(rs.getInt("hgl_id"));
				h.setHglName(rs.getString("hgl_name"));
				h.setHglGrade(rs.getInt("hgl_grade"));
				h.setHglTel(rs.getString("hgl_tel"));
				h.setHglBirth(rs.getDate("hgl_birth"));
				list.add(h);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Hgl> selectByName(String name) {
		List<Hgl> list =new ArrayList<Hgl>();
		DBHelper db=new DBHelper();
		Connection conn= db.getConn();
		String sql="select * from hgl where hgl_name like ?";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			String name1="%"+name+"%";
			ps.setString(1,name1);
			
			rs=ps.executeQuery();
			while(rs.next()){
				Hgl h = new Hgl();
				h.setHglId(rs.getInt("hgl_id"));
				h.setHglName(rs.getString("hgl_name"));
				h.setHglGrade(rs.getInt("hgl_grade"));
				h.setHglTel(rs.getString("hgl_tel"));
				h.setHglBirth(rs.getDate("hgl_birth"));
				list.add(h);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
		
		
	}

}
